﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Base.Common;
using System.Data.SqlClient;

namespace Base.DataAccess.Common
{
    public class RoleDAL
    {
        private SQLHelper sqHelper = new SQLHelper();

        public DataSet GetRole(string Language, int PageSize, int PageIndex, string Sort, string Dir, string RoleName)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(1);
            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.String;
            parameters[0].ParameterName = "@Language";
            parameters[0].Value = Language;


            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendFormat(" select r.*,value.Value as RoleName");
            sbSql.AppendFormat(" from Com_Role r");
            sbSql.AppendFormat(" left join Com_Value value on r.RoleNameId=value.Id");
            sbSql.AppendFormat(" left join Com_Language lang on value.LanguageId=lang.Id and lang.Name=@Language WHERE IsDelete =0  ");
            if (!string.IsNullOrEmpty(RoleName))// 增加“角色名称”查询
            {
                sbSql.AppendFormat("AND value.Value LIKE '%{0}%'", RoleName);
            }
            return sqHelper.ExecuteDataSet(sbSql.ToString(), false, PageSize, PageIndex, Sort, Dir, parameters);
        }



        public bool AddRole(string Language, string RoleName, string Description)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(3);
            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.String;
            parameters[0].ParameterName = "@Language";
            parameters[0].Value = Language;

            parameters[1] = (SqlParameter)sqHelper.GetParameter();
            parameters[1].DbType = DbType.String;
            parameters[1].ParameterName = "@Name";
            parameters[1].Value = RoleName;

            parameters[2] = (SqlParameter)sqHelper.GetParameter();
            parameters[2].DbType = DbType.String;
            parameters[2].ParameterName = "@Description";
            parameters[2].Value = Description;

            int result = sqHelper.ExecuteCommand("SP_Com_AddRole", true, parameters);
            if (result != -1) return true;
            else return false;

        }

        public DataTable GetRoleById(string Language, Guid Id)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(2);
            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.String;
            parameters[0].ParameterName = "@Language";
            parameters[0].Value = Language;

            parameters[1] = (SqlParameter)sqHelper.GetParameter();
            parameters[1].DbType = DbType.Guid;
            parameters[1].ParameterName = "@Id";
            parameters[1].Value = Id;

            return sqHelper.ExecuteDataTable("SP_Com_GetRoleById", true, parameters);
        }

        public bool UpdateRole(string Language, Guid Id, string RoleName, string Description)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(4);
            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.String;
            parameters[0].ParameterName = "@Language";
            parameters[0].Value = Language;

            parameters[1] = (SqlParameter)sqHelper.GetParameter();
            parameters[1].DbType = DbType.Guid;
            parameters[1].ParameterName = "@Id";
            parameters[1].Value = Id;

            parameters[2] = (SqlParameter)sqHelper.GetParameter();
            parameters[2].DbType = DbType.String;
            parameters[2].ParameterName = "@Name";
            parameters[2].Value = RoleName;

            parameters[3] = (SqlParameter)sqHelper.GetParameter();
            parameters[3].DbType = DbType.String;
            parameters[3].ParameterName = "@Description";
            parameters[3].Value = Description;

            int result = sqHelper.ExecuteCommand("SP_Com_UpdateRole", true, parameters);
            if (result != -1) return true;
            else return false;

        }

        public bool DeleteRole(Guid Id)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(1);
            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.Guid;
            parameters[0].ParameterName = "@Id";
            parameters[0].Value = Id;
            int result = sqHelper.ExecuteCommand("SP_Com_DeleteRole", true, parameters);
            if (result != -1) return true;
            else return false;
        }

        public DataTable GetMenuRole(string Language, Guid Id)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(2);
            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.String;
            parameters[0].ParameterName = "@Language";
            parameters[0].Value = Language;

            parameters[1] = (SqlParameter)sqHelper.GetParameter();
            parameters[1].DbType = DbType.Guid;
            parameters[1].ParameterName = "@Id";
            parameters[1].Value = Id;

            return sqHelper.ExecuteDataTable("SP_Com_GetMenuByRoleId", true, parameters);
        }

        public void AddRoleMenu(Guid RoleId, Guid MenuId)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(2);
            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.Guid;
            parameters[0].ParameterName = "@RoleId";
            parameters[0].Value = RoleId;

            parameters[1] = (SqlParameter)sqHelper.GetParameter();
            parameters[1].DbType = DbType.Guid;
            parameters[1].ParameterName = "@MenuId";
            parameters[1].Value = MenuId;

            sqHelper.ExecuteCommand("SP_Com_AddRoleMenu", true, parameters);
        }

        public void DeleteRoleMenu(Guid RoleId)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(1);
            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.Guid;
            parameters[0].ParameterName = "@RoleId";
            parameters[0].Value = RoleId;

            sqHelper.ExecuteCommand("SP_Com_DeleteRoleMenu", true, parameters);
        }

        public DataTable GetUserRole(Guid RoleId)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(1);
            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.Guid;
            parameters[0].ParameterName = "@RoleId";
            parameters[0].Value = RoleId;

            return sqHelper.ExecuteDataTable("SP_Com_GetUserRole", true, parameters);
        }

        public DataTable GetUserNotInRole(Guid RoleId, string Employee)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(2);
            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.Guid;
            parameters[0].ParameterName = "@RoleId";
            parameters[0].Value = RoleId;

            parameters[1] = (SqlParameter)sqHelper.GetParameter();
            parameters[1].DbType = DbType.String;
            parameters[1].ParameterName = "@Employee";
            if (!string.IsNullOrEmpty(Employee))
                parameters[1].Value = Employee;
            else parameters[1].Value = DBNull.Value;

            return sqHelper.ExecuteDataTable("SP_Com_GetUserNoInRole", true, parameters);
        }

        public bool AddUserRole(Guid UserId, Guid RoleId)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(2);
            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.Guid;
            parameters[0].ParameterName = "@UserId";
            parameters[0].Value = UserId;

            parameters[1] = (SqlParameter)sqHelper.GetParameter();
            parameters[1].DbType = DbType.Guid;
            parameters[1].ParameterName = "@RoleId";
            parameters[1].Value = RoleId;

            int result = sqHelper.ExecuteCommand("SP_Com_AddUserToRole", true, parameters);
            if (result != -1) return true;
            else return false;
        }

        public bool DeleteUserRole(Guid UserId, Guid RoleId)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(2);
            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.Guid;
            parameters[0].ParameterName = "@UserId";
            parameters[0].Value = UserId;

            parameters[1] = (SqlParameter)sqHelper.GetParameter();
            parameters[1].DbType = DbType.Guid;
            parameters[1].ParameterName = "@RoleId";
            parameters[1].Value = RoleId;

            int result = sqHelper.ExecuteCommand("SP_Com_DeleteUserFromRole", true, parameters);
            if (result != -1) return true;
            else return false;
        }
    }
}
